package org.zjvis.datascience.common.util.db;

import cn.hutool.core.convert.Convert;
import cn.hutool.db.Entity;
import org.apache.commons.lang3.StringUtils;
import org.zjvis.datascience.common.dto.dataset.DatasetColumnDTO;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Type;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;

/**
 * @description : SQL查询数据头帮助类
 * @date 2021-10-15
 */
public class HandleHelper extends cn.hutool.db.handler.HandleHelper {

    /**
     * 处理单条数据
     *
     * @param columnCount      列数
     * @param meta             ResultSetMetaData
     * @param rs               数据集
     * @param caseInsensitive  是否大小写不敏感
     * @param newTableName     新表名
     * @param columnNameMapper
     * @param databaseType     数据库类型
     * @param preview          是否用于预览
     * @return 每一行的Entity
     * @throws SQLException SQL执行异常
     * @since 4.5.16
     */
    public static Entity handleRow(int columnCount, ResultSetMetaData meta, ResultSet rs, boolean caseInsensitive
            , String newTableName, Function<String, String> columnNameMapper, String databaseType, boolean preview,
                                   List<DatasetColumnDTO> columnMessage) throws SQLException {
        return handleRow(new Entity(null, caseInsensitive), columnCount, meta, rs, newTableName, columnNameMapper, databaseType, preview, columnMessage);
    }

    /**
     * 处理单条数据
     *
     * @param <T>              Entity及其子对象
     * @param row              Entity对象
     * @param columnCount      列数
     * @param meta             ResultSetMetaData
     * @param rs               数据集
     * @param newTableName     新表名
     * @param columnNameMapper
     * @param databaseType     数据库类型
     * @param preview          是否用于预览
     * @return 每一行的Entity
     * @throws SQLException SQL执行异常
     * @since 3.3.2
     */
    public static <T extends Entity> T handleRow(T row, int columnCount, ResultSetMetaData meta, ResultSet rs
            , String newTableName, Function<String, String> columnNameMapper, String databaseType, boolean preview,
                                                 List<DatasetColumnDTO> columnMessage) throws SQLException {
        Map<String, String> columnConfig = handleColumnConfig(columnMessage);
        int type;
        for (int i = 1; i <= columnCount; i++) {
            type = meta.getColumnType(i);
            //head和body字段名统一转小写
            String colName = columnNameMapper == null ? meta.getColumnLabel(i).toLowerCase() : columnNameMapper.apply(meta.getColumnLabel(i).toLowerCase());

            // Oracle分页后会自动添加 "rownum_" 字段，为防止影响后续数据导入，在此处生成Entity时跳过
            if ("oracle".equalsIgnoreCase(databaseType) && "rownum_".equalsIgnoreCase(colName)) {
                continue;
            }
            String maskingType = columnConfig.get(colName);
            row.put(colName, getColumnValue(rs, i, type, null, preview, maskingType));
        }

        if (StringUtils.isNoneBlank(newTableName)) {
            row.setTableName(newTableName);
        }
        row.setFieldNames(row.keySet());

        return row;
    }

    /**
     * 获取字段值<br>
     * 针对日期时间等做单独处理判断
     *
     * @param <T>              返回类型
     * @param rs               {@link ResultSet}
     * @param columnIndex      字段索引
     * @param type             字段类型，默认Object
     * @param targetColumnType 结果要求的类型，需进行二次转换（null或者Object不转换）
     * @param preview          是否用于预览
     * @return 字段值
     * @throws SQLException SQL异常
     */
    public static <T> Object getColumnValue(ResultSet rs, int columnIndex, int type, Type targetColumnType, boolean preview, String maskingType) throws SQLException {
        Object rawValue = null;
        switch (type) {
            case Types.TIMESTAMP:
                try {
                    rawValue = rs.getTimestamp(columnIndex);
                } catch (SQLException ignore) {
                    // issue#776@Github
                    // 当数据库中日期为0000-00-00 00:00:00报错，转为null
                }
                break;
            case Types.TIME:
                rawValue = rs.getTime(columnIndex);
                break;
            case Types.INTEGER:
            case Types.BIGINT:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.DECIMAL:
            case Types.NUMERIC:
                //数字类型为空时转为默认值0
                rawValue = rs.getString(columnIndex) == null ? 0 : rs.getObject(columnIndex);
                break;
            case Types.LONGNVARCHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.VARCHAR:
                rawValue = rs.getString(columnIndex) == null ? "" : rs.getString(columnIndex).replaceAll("\u0000", "");
                break;
            case Types.LONGVARBINARY:
            case Types.CLOB:
            case Types.NCLOB:
            case Types.ROWID:
                rawValue = rs.getString(columnIndex) == null ? null : rs.getString(columnIndex).replaceAll("\u0000", "");
                break;
            case Types.BLOB:
                //预览时blob数据不予直接展示
                if (preview) {
                    rawValue = rs.getBlob(columnIndex) == null ? null : "(BLOB) " + rs.getBlob(columnIndex).length() + " bytes";
                    break;
                }
                if (rs.getBlob(columnIndex) == null) {
                    rawValue = null;
                    break;
                }
                InputStream in = rs.getBlob(columnIndex).getBinaryStream();
                ByteArrayOutputStream bo = new ByteArrayOutputStream();
                byte[] buffer = new byte[1024];
                int len;
                try {
                    while ((len = in.read(buffer)) != -1) {
                        bo.write(buffer, 0, len);
                    }
                    rawValue = bo.toByteArray();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                break;
            default:
                rawValue = rs.getObject(columnIndex);
        }

        if (StringUtils.isNotBlank(maskingType)) {
            try {
                switch (maskingType) {
                    case "md5":
                        rawValue = CryptoUtil.hmacMD5(String.valueOf(rawValue));
                        break;
                    case "sha1":
                        rawValue = CryptoUtil.hmacSHA1(String.valueOf(rawValue));
                        break;
                    case "mosaic":
                        rawValue = CryptoUtil.stringMosaic(String.valueOf(rawValue));
                        break;
                    default:
                        break;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (null == targetColumnType || Object.class == targetColumnType) {
            // 无需转换
            return rawValue;
        } else {
            // 按照返回值要求转换
            return Convert.convert(targetColumnType, rawValue);
        }
    }

    /**
     * 处理字段配置信息
     *
     * @param columnMessage
     * @return
     */
    private static Map<String, String> handleColumnConfig(List<DatasetColumnDTO> columnMessage) {
        Map<String, String> res = new HashMap<>();
        if (columnMessage != null) {
            for (DatasetColumnDTO col : columnMessage) {
                if (col.getImportColumn()) {
                    res.put(col.getName().toLowerCase(), col.getDataMaskingType());
                }
            }
        }
        return res;
    }

}
